package com.wanmait.cookbook.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.cookbook.vo.IngredientsType;
import com.wanmait.cookbook.vo.Pager;
import com.wanmait.demo.util.DBHelper;

/*
<<<<<<< HEAD
 * 食锟侥凤拷锟斤拷一锟斤拷锟斤拷DAO
 * 椋熸潗鍒嗙被涓�绾ц〃DAO
=======
 * 食材分类一级表DAO
>>>>>>> branch 'master' of https://gitee.com/dian-bin/cookbook.git
 */
public class IngredientsTypeDAO {
	private IngredientsTypeDAO() {}
	private static IngredientsTypeDAO ingredientsTypeDAO = new IngredientsTypeDAO();
	public static IngredientsTypeDAO getIngredientsTypeDAO() {
		return ingredientsTypeDAO;
	}
	
	//查找所有的一级食材分类
		public List<IngredientsType> findByIngredientsType()
		{
			List<IngredientsType> ingredientsTypes=new ArrayList<>();
			IngredientsType ingredientsType = null;
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			try {
				String sql="SELECT * FROM ingredients_type WHERE ENABLE=1 ";
				conn=DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				rs=ps.executeQuery();
				while(rs.next())
				{
					ingredientsType = new IngredientsType();
					ingredientsType.setId(rs.getInt("id"));
					ingredientsType.setTitle(rs.getString("title"));
					ingredientsType.setEnable(rs.getBoolean("enable"));
					ingredientsTypes.add(ingredientsType);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally 
			{
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return ingredientsTypes;
		}
	
	
	//查找所有的一级食材分类  分页
	public List<IngredientsType> findByIngredientsType(Pager pager)
	{
		List<IngredientsType> ingredientsTypes=new ArrayList<>();
		IngredientsType ingredientsType = null;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			String sql="SELECT * FROM ingredients_type WHERE ENABLE=1 limit ?,?";
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			while(rs.next())
			{
				ingredientsType = new IngredientsType();
				ingredientsType.setId(rs.getInt("id"));
				ingredientsType.setTitle(rs.getString("title"));
				ingredientsType.setEnable(rs.getBoolean("enable"));
				ingredientsTypes.add(ingredientsType);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally 
		{
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		return ingredientsTypes;
	}
	//查找所有的一级食材记录行
	public int findIngredientsTypeRows()
	{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			String sql="SELECT count(*) c  FROM ingredients_type WHERE ENABLE=1 ";
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			int rows=rs.getInt("c");
			return rows;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally 
		{
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		return 0;
	}
	//通过一级类型ID删除一级类型
	public void deleteIngredientsTypeById(int id) {
		String sql="update ingredients_type set enable=0 where id=?";
		DBHelper.getHelper().executeUpdate(sql, id);
	}
	//通过一级类型IDs删除多个一级类型
	public void deleteIngredientsTypesByIds(String ids) {
		String sql="update ingredients_type set enable=0 where id in("+ids+")";
		DBHelper.getHelper().executeUpdate(sql);
	}
	
	//通过一级食材类型ID修改类型名
	public void updateIngredientsTypeById(String val,int id) {
		String sql="UPDATE ingredients_type SET title=? WHERE id=?";
		DBHelper.getHelper().executeUpdate(sql, val,id);
	}
	//通过类型名判断是否重复
	public int isIngredientsTypeRepeat(String title) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			String sql="SELECT count(*) c  FROM ingredients_type WHERE title=? ";
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setString(1, title);
			rs=ps.executeQuery();
			rs.next();
			int rows=rs.getInt("c");
			return rows;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally 
		{
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		return 0;
	}
	
	//添加一级食材分类
	public void addIngredientsType(String title) {
		String sql="INSERT INTO ingredients_type(title) VALUES(?)";
		DBHelper.getHelper().executeUpdate(sql, title);
	}
	
	//查找所有的一级食材记录行 通过搜索
		public int findIngredientsTypeRowsByInput(String val)
		{
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			try {
				String sql="SELECT count(*) c  FROM ingredients_type WHERE ENABLE=1 and title like ?";
				conn=DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				ps.setString(1, "%"+val+"%");
				rs=ps.executeQuery();
				rs.next();
				int rows=rs.getInt("c");
				return rows;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally 
			{
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return 0;
		}
		
		
		//查找所有的一级食材分类  分页 通过搜索关键字
		public List<IngredientsType> findByIngredientsTypeByInput(Pager pager,String val)
		{
			List<IngredientsType> ingredientsTypes=new ArrayList<>();
			IngredientsType ingredientsType = null;
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			try {
				String sql="SELECT * FROM ingredients_type WHERE ENABLE=1 and title like ? limit ?,?";
				conn=DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				ps.setString(1, "%"+val+"%");
				ps.setInt(2, pager.getStart());
				ps.setInt(3, pager.getPageSize());
				rs=ps.executeQuery();
				while(rs.next())
				{
					ingredientsType = new IngredientsType();
					ingredientsType.setId(rs.getInt("id"));
					ingredientsType.setTitle(rs.getString("title"));
					ingredientsType.setEnable(rs.getBoolean("enable"));
					ingredientsTypes.add(ingredientsType);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally 
			{
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return ingredientsTypes;
		}
	
}
